# -*- coding: utf-8 -*-
"""
数据库工具模块
负责数据库连接管理、数据查询和写入操作
"""
import sqlite3
import pandas as pd
import numpy as np
import datetime
import warnings
from config_utils import load_and_validate_config
from data_cleaner import store_data_cleaner,correct_zhanqu
# 加载配置
CONFIG = load_and_validate_config()


def get_db_connection(db_path=None):
    """
    获取数据库连接
    :param db_path: 数据库路径，默认使用配置文件中的路径
    :return: 数据库连接对象
    """
    db_path = db_path or CONFIG['db_path']
    try:
        conn = sqlite3.connect(db_path)
        return conn
    except Exception as e:
        print(f'数据库连接失败: {e}')
        return None


def read_store_sign_from_db():
    """
    从数据库读取门店标识（门店编号-老店）信息
    :return: 包含门店标识数据的DataFrame
    """

    # 查询门店标识数据
    with get_db_connection() as conn:
        return pd.read_sql_query("""
            SELECT * FROM store_sign
        """, conn)


def query_store_info_from_db():
    """
    从数据库查询门店信息
    :return: 包含门店信息的DataFrame
    """
    query = """
        SELECT 门店编号, 门店名称, 大区经理 as 省区经理, 省区经理 as 区域经理, 区域经理 as 督导,
               南北战区 as 战区, 运营状态, 收银机ID, 省, 市, 区
        FROM current
    """
    
    with get_db_connection() as conn:
        return pd.read_sql_query(query, conn)

def write_data_to_db():
    """
    将Excel数据写入数据库
    包含数据读取、清洗、转换和存储逻辑
    """
    print('开始读取门店信息文件：')
    try:
        # 读取Excel文件
        with warnings.catch_warnings():
            warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl.styles.stylesheet")
            df = pd.read_excel(
                CONFIG['file_path'], 
                header=1, 
                dtype=str, 
                sheet_name='门店信息表', 
                usecols=CONFIG['use_columns']
            )
        # 重命名列为老格式
        df = df.rename(columns=CONFIG['rename_columns'])
    except Exception as e:
        print(f'读取Excel文件失败: {e}')
        return
    
    # 添加更新日期
    now = datetime.datetime.now()
    formatted_date = now.strftime('%Y/%m/%d')
    df['更新日期'] = formatted_date
    print('进入清洗模块')
    df = store_data_cleaner(df)
    df = df.loc[:, ['门店编号','门店名称','门店类型','门店详细地址','大区经理','省区经理','区域经理','南北战区','法人','法人电话',
                     '法人身份证号码','运营状态','收银机ID','省','市','区','收货人联系方式','U8C客商编码','发货仓库','更新日期'
                    ]]
    with get_db_connection() as conn:
        print('更新info表')
        df.to_sql(name='info', con=conn, if_exists='append', index=False)
        
        # 查询最近数据并处理
        year_query = """
            SELECT * FROM (
                SELECT *, ROW_NUMBER() OVER (PARTITION BY "门店编号" ORDER BY "更新日期" DESC) AS rn
                FROM info
                WHERE "更新日期" LIKE '2024%' OR "更新日期" LIKE '2025%'
            ) t WHERE rn = 1;
        """
        
        df_year = pd.read_sql_query(year_query, conn)
        df_year['更新日期'] = pd.to_datetime(df_year['更新日期'])
        df_year['运营状态'] = df_year.apply(
            lambda row: f'已解约_{row["更新日期"].year}' if row['更新日期'].date() < now.date() else row['运营状态'], 
            axis=1
        )
        df_year = correct_zhanqu(df_year)
        df_year = df_year.replace('nan', np.nan)
        print('更新current表')
        df_year.to_sql(name='current', con=conn, if_exists='replace', index=False)
        print(f'数据已成功写入数据库，共{len(df_year)}行')
        print('今日数据已导入数据库！')